{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f9731b5-1d23-4405-bba7-395d6011d75a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pymysql\n",
    "import pandas as pd\n",
    "import pymysql.cursors\n",
    "\n",
    "class MysqlUtils(object):\n",
    "    \"\"\"数据库工具类\n",
    "    \"\"\"\n",
    "    def __init__(self):\n",
    "        self.conn = pymysql.connect(\n",
    "            host='127.0.0.1',\n",
    "            user='root',\n",
    "            passwd='root',\n",
    "            port=3306,\n",
    "            database='scenic',\n",
    "            charset='utf8'\n",
    "        )\n",
    "\n",
    "    def get_scenic_data(self):\n",
    "        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)\n",
    "        sql = \"\"\"\n",
    "        SELECT order_id, age, user_count, id_no, user_name, phone FROM (SELECT o.id as order_id, u.id_no, u.phone, u.user_name,\n",
    "        case \n",
    "            when length(u.id_no) = 18 THEN YEAR(NOW()) - CAST(substr(u.id_no, 7, 4) as signed)\n",
    "            else NULL\n",
    "        end as age,\n",
    "        (SELECT count(*) FROM ticket_order_user_rel WHERE order_id = o.id ) as user_count\n",
    "        FROM ticket_order o JOIN ticket_order_user_rel u on u.order_id = o.id WHERE u.id_no is not null and u.id_no != '' ) as subquery\n",
    "        WHERE user_count = 1 and (age < 18 or age >= 60)\n",
    "        \"\"\"\n",
    "        cursor.execute(sql)\n",
    "        ret = cursor.fetchall()\n",
    "        df = pd.DataFrame(ret)\n",
    "        # print(df.head())\n",
    "        df.to_csv('./outlier/scenic_data.csv')\n",
    "\n",
    "if __name__ == '__main__':\n",
    "    mu = MysqlUtils()\n",
    "    mu.get_scenic_data()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
